package io.aicn.lowcode.service;

import com.fasterxml.jackson.databind.ObjectMapper;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.util.*;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

/**
 * SQL解析器服务
 * 负责解析CREATE TABLE语句并转换为JSON Schema格式
 */
@Service
public class SqlParserService {

    @Autowired
    private ObjectMapper objectMapper;

    /**
     * 解析CREATE TABLE语句
     * @param createTableSql CREATE TABLE语句
     * @return 解析后的表结构JSON
     */
    public Map<String, Object> parseCreateTableSql(String createTableSql) {
        try {
            // 清理SQL语句
            String cleanSql = cleanSql(createTableSql);
            
            // 解析表名
            String tableName = parseTableName(cleanSql);
            
            // 解析表注释
            String tableComment = parseTableComment(cleanSql);
            
            // 解析字段定义
            List<Map<String, Object>> columns = parseColumns(cleanSql);
            
            // 解析主键
            Map<String, Object> primaryKey = parsePrimaryKey(cleanSql, columns);
            
            // 解析索引
            List<Map<String, Object>> indexes = parseIndexes(cleanSql);
            
            // 解析表选项
            Map<String, Object> tableOptions = parseTableOptions(cleanSql);
            
            // 构建表结构JSON
            Map<String, Object> tableSchema = new HashMap<>();
            tableSchema.put("tableName", tableName);
            tableSchema.put("comment", tableComment);
            tableSchema.put("columns", columns);
            
            if (primaryKey != null) {
                tableSchema.put("primaryKey", primaryKey);
            }
            
            if (!indexes.isEmpty()) {
                tableSchema.put("indexes", indexes);
            }
            
            // 添加表选项
            tableSchema.putAll(tableOptions);
            
            return tableSchema;
            
        } catch (Exception e) {
            throw new RuntimeException("解析CREATE TABLE语句失败: " + e.getMessage(), e);
        }
    }

    /**
     * 清理SQL语句，移除多余的空格和换行
     */
    private String cleanSql(String sql) {
        return sql.replaceAll("\\s+", " ")
                  .replaceAll("\\n", " ")
                  .replaceAll("\\r", " ")
                  .trim();
    }

    /**
     * 解析表名
     */
    private String parseTableName(String sql) {
        Pattern pattern = Pattern.compile("CREATE\\s+TABLE\\s+`?([^`\\s]+)`?\\s*\\(", Pattern.CASE_INSENSITIVE);
        Matcher matcher = pattern.matcher(sql);
        if (matcher.find()) {
            return matcher.group(1);
        }
        throw new RuntimeException("无法解析表名");
    }

    /**
     * 解析表注释
     */
    private String parseTableComment(String sql) {
        Pattern pattern = Pattern.compile("COMMENT\\s*=\\s*'([^']*)'", Pattern.CASE_INSENSITIVE);
        Matcher matcher = pattern.matcher(sql);
        if (matcher.find()) {
            return matcher.group(1);
        }
        return "";
    }

    /**
     * 解析字段定义
     */
    private List<Map<String, Object>> parseColumns(String sql) {
        List<Map<String, Object>> columns = new ArrayList<>();
        
        // 提取字段定义部分
        Pattern tablePattern = Pattern.compile("CREATE\\s+TABLE\\s+`?[^`\\s]+`?\\s*\\((.*)\\)\\s*ENGINE", Pattern.CASE_INSENSITIVE | Pattern.DOTALL);
        Matcher tableMatcher = tablePattern.matcher(sql);
        
        if (!tableMatcher.find()) {
            throw new RuntimeException("无法解析表结构");
        }
        
        String columnsPart = tableMatcher.group(1);
        
        // 分割字段定义（处理嵌套的括号）
        List<String> columnDefinitions = splitColumnDefinitions(columnsPart);
        
        for (String columnDef : columnDefinitions) {
            columnDef = columnDef.trim();
            
            // 跳过主键、索引等约束定义
            if (columnDef.toUpperCase().startsWith("PRIMARY KEY") ||
                columnDef.toUpperCase().startsWith("UNIQUE KEY") ||
                columnDef.toUpperCase().startsWith("KEY") ||
                columnDef.toUpperCase().startsWith("INDEX") ||
                columnDef.toUpperCase().startsWith("CONSTRAINT")) {
                continue;
            }
            
            Map<String, Object> column = parseColumnDefinition(columnDef);
            if (column != null) {
                columns.add(column);
            }
        }
        
        return columns;
    }

    /**
     * 分割字段定义，处理嵌套括号
     */
    private List<String> splitColumnDefinitions(String columnsPart) {
        List<String> definitions = new ArrayList<>();
        StringBuilder current = new StringBuilder();
        int parenthesesLevel = 0;
        boolean inQuotes = false;
        char quoteChar = 0;
        
        for (int i = 0; i < columnsPart.length(); i++) {
            char c = columnsPart.charAt(i);
            
            if (!inQuotes && (c == '\'' || c == '"' || c == '`')) {
                inQuotes = true;
                quoteChar = c;
            } else if (inQuotes && c == quoteChar) {
                inQuotes = false;
            } else if (!inQuotes) {
                if (c == '(') {
                    parenthesesLevel++;
                } else if (c == ')') {
                    parenthesesLevel--;
                } else if (c == ',' && parenthesesLevel == 0) {
                    definitions.add(current.toString().trim());
                    current = new StringBuilder();
                    continue;
                }
            }
            
            current.append(c);
        }
        
        if (current.length() > 0) {
            definitions.add(current.toString().trim());
        }
        
        return definitions;
    }

    /**
     * 解析单个字段定义
     */
    private Map<String, Object> parseColumnDefinition(String columnDef) {
        if (columnDef.isEmpty()) {
            return null;
        }
        
        Map<String, Object> column = new HashMap<>();
        
        // 解析字段名
        Pattern namePattern = Pattern.compile("^`?([^`\\s]+)`?\\s+(.*)$");
        Matcher nameMatcher = namePattern.matcher(columnDef);
        
        if (!nameMatcher.find()) {
            return null;
        }
        
        String columnName = nameMatcher.group(1);
        String restDef = nameMatcher.group(2);
        
        column.put("name", columnName);
        
        // 解析数据类型
        parseDataType(restDef, column);
        
        // 解析约束
        parseConstraints(restDef, column);
        
        // 解析注释
        parseColumnComment(restDef, column);
        
        return column;
    }

    /**
     * 解析数据类型
     */
    private void parseDataType(String def, Map<String, Object> column) {
        // 匹配数据类型（包括长度和精度）
        Pattern typePattern = Pattern.compile("^(\\w+)(?:\\((\\d+)(?:,(\\d+))?\\))?", Pattern.CASE_INSENSITIVE);
        Matcher typeMatcher = typePattern.matcher(def);
        
        if (typeMatcher.find()) {
            String dataType = typeMatcher.group(1).toUpperCase();
            String length = typeMatcher.group(2);
            String decimals = typeMatcher.group(3);
            
            column.put("type", dataType);
            
            if (length != null) {
                column.put("length", Integer.parseInt(length));
            }
            
            if (decimals != null) {
                column.put("decimals", Integer.parseInt(decimals));
            }
        }
    }

    /**
     * 解析字段约束
     */
    private void parseConstraints(String def, Map<String, Object> column) {
        String upperDef = def.toUpperCase();
        
        // NOT NULL
        column.put("nullable", !upperDef.contains("NOT NULL"));
        
        // AUTO_INCREMENT
        column.put("autoIncrement", upperDef.contains("AUTO_INCREMENT"));
        
        // UNSIGNED
        column.put("unsigned", upperDef.contains("UNSIGNED"));
        
        // DEFAULT值
        Pattern defaultPattern = Pattern.compile("DEFAULT\\s+('([^']*)'|([^\\s,]+))", Pattern.CASE_INSENSITIVE);
        Matcher defaultMatcher = defaultPattern.matcher(def);
        if (defaultMatcher.find()) {
            String defaultValue = defaultMatcher.group(2) != null ? defaultMatcher.group(2) : defaultMatcher.group(3);
            if (!"NULL".equalsIgnoreCase(defaultValue)) {
                column.put("defaultValue", defaultValue);
            }
        }
    }

    /**
     * 解析字段注释
     */
    private void parseColumnComment(String def, Map<String, Object> column) {
        Pattern commentPattern = Pattern.compile("COMMENT\\s+'([^']*)'", Pattern.CASE_INSENSITIVE);
        Matcher commentMatcher = commentPattern.matcher(def);
        if (commentMatcher.find()) {
            column.put("comment", commentMatcher.group(1));
        }
    }

    /**
     * 解析主键
     */
    private Map<String, Object> parsePrimaryKey(String sql, List<Map<String, Object>> columns) {
        // 先检查字段定义中的主键
        for (Map<String, Object> column : columns) {
            if (Boolean.TRUE.equals(column.get("autoIncrement"))) {
                Map<String, Object> primaryKey = new HashMap<>();
                primaryKey.put("columns", Arrays.asList(column.get("name")));
                return primaryKey;
            }
        }
        
        // 再检查PRIMARY KEY约束
        Pattern pkPattern = Pattern.compile("PRIMARY\\s+KEY\\s*\\(([^)]+)\\)", Pattern.CASE_INSENSITIVE);
        Matcher pkMatcher = pkPattern.matcher(sql);
        if (pkMatcher.find()) {
            String columnsList = pkMatcher.group(1);
            List<String> pkColumns = new ArrayList<>();
            for (String col : columnsList.split(",")) {
                pkColumns.add(col.trim().replaceAll("`", ""));
            }
            
            Map<String, Object> primaryKey = new HashMap<>();
            primaryKey.put("columns", pkColumns);
            return primaryKey;
        }
        
        return null;
    }

    /**
     * 解析索引
     */
    private List<Map<String, Object>> parseIndexes(String sql) {
        List<Map<String, Object>> indexes = new ArrayList<>();
        
        // 解析UNIQUE KEY
        Pattern uniquePattern = Pattern.compile("UNIQUE\\s+KEY\\s+`?([^`\\s]+)`?\\s*\\(([^)]+)\\)", Pattern.CASE_INSENSITIVE);
        Matcher uniqueMatcher = uniquePattern.matcher(sql);
        while (uniqueMatcher.find()) {
            Map<String, Object> index = new HashMap<>();
            index.put("name", uniqueMatcher.group(1));
            index.put("type", "UNIQUE");
            
            List<String> columns = new ArrayList<>();
            for (String col : uniqueMatcher.group(2).split(",")) {
                columns.add(col.trim().replaceAll("`", ""));
            }
            index.put("columns", columns);
            
            indexes.add(index);
        }
        
        // 解析普通KEY
        Pattern keyPattern = Pattern.compile("(?<!UNIQUE\\s)(?<!PRIMARY\\s)KEY\\s+`?([^`\\s]+)`?\\s*\\(([^)]+)\\)", Pattern.CASE_INSENSITIVE);
        Matcher keyMatcher = keyPattern.matcher(sql);
        while (keyMatcher.find()) {
            Map<String, Object> index = new HashMap<>();
            index.put("name", keyMatcher.group(1));
            index.put("type", "INDEX");
            
            List<String> columns = new ArrayList<>();
            for (String col : keyMatcher.group(2).split(",")) {
                columns.add(col.trim().replaceAll("`", ""));
            }
            index.put("columns", columns);
            
            indexes.add(index);
        }
        
        return indexes;
    }

    /**
     * 解析表选项
     */
    private Map<String, Object> parseTableOptions(String sql) {
        Map<String, Object> options = new HashMap<>();
        
        // ENGINE
        Pattern enginePattern = Pattern.compile("ENGINE\\s*=\\s*(\\w+)", Pattern.CASE_INSENSITIVE);
        Matcher engineMatcher = enginePattern.matcher(sql);
        if (engineMatcher.find()) {
            options.put("engine", engineMatcher.group(1));
        }
        
        // CHARSET
        Pattern charsetPattern = Pattern.compile("(?:DEFAULT\\s+)?CHARSET\\s*=\\s*(\\w+)", Pattern.CASE_INSENSITIVE);
        Matcher charsetMatcher = charsetPattern.matcher(sql);
        if (charsetMatcher.find()) {
            options.put("charset", charsetMatcher.group(1));
        }
        
        // COLLATE
        Pattern collatePattern = Pattern.compile("COLLATE\\s*=\\s*(\\w+)", Pattern.CASE_INSENSITIVE);
        Matcher collateMatcher = collatePattern.matcher(sql);
        if (collateMatcher.find()) {
            options.put("collation", collateMatcher.group(1));
        }
        
        // AUTO_INCREMENT
        Pattern autoIncPattern = Pattern.compile("AUTO_INCREMENT\\s*=\\s*(\\d+)", Pattern.CASE_INSENSITIVE);
        Matcher autoIncMatcher = autoIncPattern.matcher(sql);
        if (autoIncMatcher.find()) {
            options.put("autoIncrement", Integer.parseInt(autoIncMatcher.group(1)));
        }
        
        // ROW_FORMAT
        Pattern rowFormatPattern = Pattern.compile("ROW_FORMAT\\s*=\\s*(\\w+)", Pattern.CASE_INSENSITIVE);
        Matcher rowFormatMatcher = rowFormatPattern.matcher(sql);
        if (rowFormatMatcher.find()) {
            options.put("rowFormat", rowFormatMatcher.group(1));
        }
        
        return options;
    }
}